﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace YZJOSM.Models
{
    class SqlScriptStore
    {
        //存储项目的SQL脚本
        //存储过程 用来 更新灭菌步间隔不够处理的
        //        USE[XIAOYI]
        //GO
        ///****** Object:  StoredProcedure [dbo].[usp_testA]    Script Date: 2022-07-11 16:21:39 ******/
        //SET ANSI_NULLS ON
        //GO
        //SET QUOTED_IDENTIFIER ON
        //GO



        //ALTER proc[dbo].[usp_testA]
        //        @logtime nvarchar(50),
        //@data decimal (10,2)
        //as
        //update tblSIPData set processStep = 4, batchStep = 1 WHERE logtime = @logtime

        //存储过程 用来 更新灭菌步间隔过多处理的
        //        USE[XIAOYI]
        //        GO
        ///****** Object:  StoredProcedure [dbo].[usp_testB]    Script Date: 2022-07-11 16:22:42 ******/
        //SET ANSI_NULLS ON
        //GO
        //SET QUOTED_IDENTIFIER ON
        //GO



        //ALTER proc[dbo].[usp_testB]
        //        @logtime nvarchar(50),
        //@data decimal (10,2)
        //as
        //update tblSIPData set processStep = 3, batchStep = 1 where logtime = @logtime

        //处理步骤时间
        //        USE[XIAOYI]
        //GO
        ///****** Object:  StoredProcedure [dbo].[usp_testD]    Script Date: 2022-07-11 16:23:08 ******/
        //SET ANSI_NULLS ON
        //GO
        //SET QUOTED_IDENTIFIER ON
        //GO



        //ALTER proc[dbo].[usp_testD]

        //        @newTime nvarchar(50),
        //@logtime nvarchar(50)
        //as
        //update tblSIPData set logtime = @newTime WHERE logtime = @logtime

        //抓取间隔数据，包含步骤启停信息
        //        Select* From tblSIPData Where logtime in 
        //  --间隔时间
        // (Select logtime From tblSIPData
        // where (logtime between '2022-07-01 00:00:00' And '2022-07-12 10:03:08')And(DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %1= 0)
        // union 
        // --抓取步骤最后一个时间
        // Select max(logtime)logtime From tblSIPData where(logtime between '2022-07-01 00:00:00' And '2022-07-12 10:03:08')group by processStep
        //union 
        // --抓取步骤第一个时间
        // Select min(logtime)logtime From tblSIPData where(logtime between '2022-07-01 00:00:00' And '2022-07-12 10:03:08')group by processStep
        //union 
        // --这段找出步序的结束时间
        // Select logtime From
        // (select logtime, processStep, lead(processStep,1, processStep)Over(order by logtime)Offset from tblSIPData
        //  where(logtime between '2022-07-01 00:00:00' And '2022-07-12 10:03:08'))_ Where processStep<> Offset
        // union 
        //  --这段找出步序的开始时间
        // Select logtime From
        // (select logtime, processStep, lag(processStep,1, processStep)Over(order by logtime)Offset from tblSIPData
        //  where(logtime between '2022-07-01 00:00:00' And '2022-07-12 10:03:08'))_ Where processStep<> Offset 

        // ) AND reportname = '_SIP_20220709095542' order by logtime
        // --以上五部分时间组成了一个包含步序启停时间及时间间隔的时间组
    }
}
